﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using EnterpriseFund.Common;

namespace EnterpriseFund.Controllers
{
    public class QueryForecastSummarySheetController : Controller
    {
        //
        // GET: /QueryForecastSummarySheet/

        public ActionResult Index()
        {
            return View();
        }

        public ActionResult ShowData()
        {
            string fyear = OperateSession.get(Common.GlobalConstant.PARAM_SYSYEAR);
            BLL.VSBZSXX VSBZSXXBLL = new BLL.VSBZSXX();
            DataSet ds = VSBZSXXBLL.GetDataforQueryAllowanceForecast((decimal.Parse(fyear)) + "-01-01", (decimal.Parse(fyear)) + "-12-31", (decimal.Parse(fyear)).ToString());
            BLL.TaxAuditAdjust TaxAuditAdjustBLL = new BLL.TaxAuditAdjust();
            DataSet yds = TaxAuditAdjustBLL.GetAllDateListByYear((decimal.Parse(fyear) - 1).ToString());
            DataTable dt = GetPolicyData(ds.Tables[0], yds.Tables[0], (decimal.Parse(fyear)).ToString());
            ViewData["showdata"] = GetShowData(fyear, dt);
            return View();
        }

        public DataTable GetShowData(string fyear,DataTable dt)
        {
            BLL.TaxClassAllowance taxClassAllowanceBLL = new BLL.TaxClassAllowance();
            List<Model.TaxClassAllowance> taxClassAllowanceList = taxClassAllowanceBLL.GetModelList("FYear=" + fyear);
            ViewData["taxClassAllowanceList"] = taxClassAllowanceList;
            DataTable newDt = GetNewData(fyear, "J", taxClassAllowanceList);

            var q = from nm in dt.AsEnumerable()
                    group nm by
                        new { key1 = nm.Field<string>("NSRDZDAH"), key2 = nm.Field<string>("NSRBM"), key3 = nm.Field<string>("NSRMC"), key4 = nm.Field<string>("NSRSBH") } into g
                    select new
                    {
                        NSRDZDAH = g.Key.key1,
                        NSRBM = g.Key.key2,
                        NSRMC = g.Key.key3,
                        NSRSBH = g.Key.key4
                    };
            int i = 1;
            foreach (var n in q)
            {
                DataRow rDr = newDt.NewRow();
                DataRow[] nDr = dt.Select(" NSRDZDAH =" + n.NSRDZDAH.ToString());
                rDr["xh"] = i;
                rDr["nsrbm"] = n.NSRBM;
                rDr["NSRMC"] = n.NSRMC;
                rDr["FFinancePropName"] = nDr[0]["FFinancePropName"];

                foreach (Model.TaxClassAllowance t in taxClassAllowanceList)
                {
                    rDr[t.FTaxClassAllowanceNo + "e"] = decimal.Round((nDr[0][t.FTaxClassAllowanceNo] == null ? 0 : decimal.Parse(nDr[0][t.FTaxClassAllowanceNo].ToString())), 0);
                }

                rDr["ygzhj"] = "0";

                foreach (Model.TaxClassAllowance t in taxClassAllowanceList)
                {
                    DataRow[] sdr = nDr.CopyToDataTable().Select("FTaxClassAllowanceNo='" + t.FTaxClassAllowanceNo + "'");
                    if (sdr.Count() > 0)
                    {
                        if (t.FTaxClassAllowanceName.Contains("增值税"))
                        {
                            if (t.FTaxClassAllowanceName.Contains("营改"))
                            {
                                rDr[t.FTaxClassAllowanceNo + "l"] = sdr[0]["FOuterRate"];
                            }
                            else
                            {
                                rDr[t.FTaxClassAllowanceNo + "base"] = sdr[0]["base"];
                                rDr[t.FTaxClassAllowanceNo + "FInnerRate"] = sdr[0]["FInnerRate"];
                                rDr[t.FTaxClassAllowanceNo + "FOuterRate"] = sdr[0]["FOuterRate"];
                            }
                        }
                        else
                        {
                            rDr[t.FTaxClassAllowanceNo + "l"] = sdr[0]["FOuterRate"];
                        }
                    }
                    else
                    {
                        if (t.FTaxClassAllowanceName.Contains("增值税"))
                        {
                            if (t.FTaxClassAllowanceName.Contains("营改"))
                            {
                                rDr[t.FTaxClassAllowanceNo + "l"] = 0;
                            }
                            else
                            {
                                rDr[t.FTaxClassAllowanceNo + "base"] = 0;
                                rDr[t.FTaxClassAllowanceNo + "FInnerRate"] = 0;
                                rDr[t.FTaxClassAllowanceNo + "FOuterRate"] = 0;
                            }
                        }
                        else
                        {
                            rDr[t.FTaxClassAllowanceNo + "l"] = 0;
                        }
                    }

                }


                foreach (Model.TaxClassAllowance t in taxClassAllowanceList)
                {
                    DataRow[] sdr = nDr.CopyToDataTable().Select("FTaxClassAllowanceNo='" + t.FTaxClassAllowanceNo + "'");
                    if (sdr.Count() > 0)
                    {
                        rDr[t.FTaxClassAllowanceNo + "k"] = sdr[0]["FAllowanceAmount"];
                    }
                    else
                    {
                        rDr[t.FTaxClassAllowanceNo + "k"] = 0;
                    }
                }

                newDt.Rows.Add(rDr);
                i++;
            }


            DataRow hjdr = newDt.NewRow();
            foreach (DataColumn dc in newDt.Columns)
            {
                if (dc.DataType == typeof(decimal))
                {
                    hjdr[dc.ColumnName] = newDt.Compute("sum([" + dc.ColumnName + "])", "");
                }
                if (dc.ColumnName == "xh")
                {
                    hjdr["xh"] = "合计";
                }

                if (dc.ColumnName == "NSRMC")
                {
                    hjdr["NSRBM"] = "共" + q.Count() + "户";
                }
            }
            newDt.Rows.Add(hjdr);
            return newDt;
        }

        public DataTable GetNewData(string year, string type, List<Model.TaxClassAllowance> taxClassAllowanceList)
        {
            DataTable newDt = new DataTable();
            newDt.Columns.Add("xh");
            newDt.Columns.Add("nsrbm");
            newDt.Columns.Add("NSRMC");
            newDt.Columns.Add("FFinancePropName");


            string e = "";
            foreach (Model.TaxClassAllowance t in taxClassAllowanceList)
            {
                newDt.Columns.Add(t.FTaxClassAllowanceNo + "e", typeof(decimal));
                if (e == "")
                {
                    e += "[" + t.FTaxClassAllowanceNo + "e]";
                }
                else
                {
                    e += "+[" + t.FTaxClassAllowanceNo + "e]";
                }
            }
            newDt.Columns.Add("ygzhj", typeof(decimal));
            newDt.Columns.Add("ehj", typeof(decimal));
            if (e == "")
            {
                e += "0-[ygzhj]";
            }
            else
            {
                e += "-[ygzhj]";
            }
            newDt.Columns["ehj"].Expression = e;

            foreach (Model.TaxClassAllowance t in taxClassAllowanceList)
            {
                if (type == "Y")
                {
                    newDt.Columns.Add(t.FTaxClassAllowanceNo + "l");
                }
                else
                {
                    if (t.FTaxClassAllowanceName.Contains("增值税"))
                    {
                        if (t.FTaxClassAllowanceName.Contains("营改"))
                        {
                            newDt.Columns.Add(t.FTaxClassAllowanceNo + "l");
                        }
                        else
                        {
                            newDt.Columns.Add(t.FTaxClassAllowanceNo + "base");
                            newDt.Columns.Add(t.FTaxClassAllowanceNo + "FInnerRate");
                            newDt.Columns.Add(t.FTaxClassAllowanceNo + "FOuterRate");
                        }
                    }
                    else
                    {
                        newDt.Columns.Add(t.FTaxClassAllowanceNo + "l");
                    }
                }
            }

            e = "";
            foreach (Model.TaxClassAllowance t in taxClassAllowanceList)
            {
                newDt.Columns.Add(t.FTaxClassAllowanceNo + "k", typeof(decimal));
                if (e == "")
                {
                    e += "[" + t.FTaxClassAllowanceNo + "k]";
                }
                else
                {
                    e += "+[" + t.FTaxClassAllowanceNo + "k]";
                }
            }

            newDt.Columns.Add("khj", typeof(decimal));
            newDt.Columns["khj"].Expression = e;
            //newDt.Columns.Add("ybtSum", typeof(decimal));

            //e = "";
            //foreach (Model.TaxClassAllowance t in taxClassAllowanceList)
            //{
            //    newDt.Columns.Add(t.FTaxClassAllowanceNo + "b", typeof(decimal));
            //    if (e == "")
            //    {
            //        e += "[" + t.FTaxClassAllowanceNo + "b]";
            //    }
            //    else
            //    {
            //        e += "+[" + t.FTaxClassAllowanceNo + "b]";
            //    }
            //}
            //newDt.Columns.Add("tstz", typeof(decimal));
            //if (e == "")
            //{
            //    e += "tstz";
            //}
            //else
            //{
            //    e += "+[tstz]";
            //}

            //newDt.Columns.Add("bhj", typeof(decimal));
            //newDt.Columns["bhj"].Expression = e;
            return newDt;
        }
        

        /// <summary>
        /// 政策鉴定过程
        /// </summary>
        /// <param name="dt">税款数据</param>
        /// <param name="yDt">上年度税款数据作为基数使用</param>
        /// <param name="y">年度</param>
        /// <returns>鉴定后数据列表</returns>
        public DataTable GetPolicyData(DataTable dt, DataTable yDt, string y)
        {
            DataTable sDt = new DataTable();
            sDt.Columns.Add("FAllowanceAmount", typeof(decimal));
            sDt.Columns.Add("FEnterprisePropName");
            sDt.Columns.Add("FTaxClassAllowanceName");
            sDt.Columns.Add("FTaxClassAllowanceNo");
            sDt.Columns.Add("FInnerRate");
            sDt.Columns.Add("FOuterRate");
            sDt.Columns.Add("base");
            //dt.Columns.Add("FEnterprisePropNo");
            BLL.SupportPolicy sBLL = new BLL.SupportPolicy();

            List<Model.SupportPolicy> sl = sBLL.GetModelList(" sp.FYear=" + y);

            DataTable rDt = GetRuleExpressionDT(dt, y);///转换数据格式

            foreach (DataColumn dc in rDt.Columns)
            {
                sDt.Columns.Add(dc.ColumnName);
            }

            BLL.YGZAllowance YGZAllowanceBLL = new BLL.YGZAllowance();
            List<Model.YGZAllowance> YGZAllowanceList = YGZAllowanceBLL.GetModelList("FYear=" + OperateSession.get(GlobalConstant.PARAM_SYSYEAR));

            DataTable cDt = GetComputeExpressionDT();///获取公式判断数据格式

            foreach (Model.SupportPolicy s in sl)
            {
                s.FRuleExpression = s.FRuleExpression.Replace("&&", " and ");
                s.FRuleExpression = s.FRuleExpression.Replace("||", " or ");

                DataRow[] rdrl = rDt.Select(s.FRuleExpression);///条件匹配

                if (rdrl.Count() > 0)
                {
                    foreach (DataRow r in rdrl)
                    {
                        if (s.FEnterprisePropNo == r["FEnterprisePropNo"].ToString())
                        {
                            ///是否为普惠曾策
                            if (s.FProp == "1")
                            {
                                if (s.FTaxClassAllowanceName.Contains("营改增"))
                                {
                                    List<Model.YGZAllowance> newYGZAllowanceList = (from nm in YGZAllowanceList where nm.NSRDZDAH == decimal.Parse(r["NSRDZDAH"].ToString()) select nm).ToList();
                                    decimal sum = newYGZAllowanceList.Sum(z => z.FAmount);
                                    r[s.FTaxClassAllowanceNo] = decimal.Parse(r[s.FTaxClassAllowanceNo].ToString()) - sum;
                                }
                                DataRow[] yDrl = yDt.Select("FTaxClassAllowanceNo='" + s.FTaxClassAllowanceNo + "' and NSRDZDAH = '" + r["NSRDZDAH"] + "'");///查询上年度数据 做基数使用
                                DataRow cDr = cDt.NewRow();
                                if (s.FIsEvalBase == "Y")
                                {
                                    cDr["se"] = r[s.FTaxClassAllowanceNo];
                                    cDr["base"] = (yDrl.Count() > 0 ? yDrl[0]["FAdjustAmount"] : 0);
                                    cDr["rateIn"] = s.FInnerRate;
                                    cDr["rateOut"] = s.FOuterRate;
                                }
                                else
                                {
                                    cDr["se"] =r[s.FTaxClassAllowanceNo];
                                    cDr["base"] = 0;
                                    cDr["rateIn"] = 0;
                                    cDr["rateOut"] = s.FOuterRate;
                                }
                                if (cDr["base"] == null || cDr["base"].ToString() == "")
                                {
                                    cDr["base"] = "0";
                                }
                                cDt.Rows.Clear();
                                cDt.Rows.Add(cDr);
                                cDt.Columns["zcjd"].Expression = s.FComputeExpression;///公式计算补贴额

                                DataRow sDr = sDt.NewRow();
                                sDr["FAllowanceAmount"] = decimal.Round((decimal.Parse(cDt.Rows[0]["zcjd"].ToString()) / 10000), 0);
                                sDr["FInnerRate"] = s.FInnerRate == null ? 0 : decimal.Round(s.FInnerRate.Value * 100,2);
                                sDr["FOuterRate"] = s.FOuterRate == null ? 0 : decimal.Round(s.FOuterRate.Value * 100, 2);
                                sDr["base"] = decimal.Round((cDr["base"] == null ? 0 : decimal.Parse(cDr["base"].ToString())), 0);
                                sDr["FEnterprisePropName"] = s.FEnterprisePropName;
                                sDr["FTaxClassAllowanceName"] = s.FTaxClassAllowanceName;
                                sDr["FTaxClassAllowanceNo"] = s.FTaxClassAllowanceNo;
                          
                                foreach (DataColumn dc in rDt.Columns)
                                {
                                    sDr[dc.ColumnName] =r[dc.ColumnName] ;
                                }
                                sDt.Rows.Add(sDr);
                            }
                        }
                    }
                }
            }

            return sDt;
        }

        /// <summary>
        /// 数据格式转换装换为条件判断格式
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="y"></param>
        /// <returns></returns>
        public DataTable GetRuleExpressionDT(DataTable dt, string y)
        {
            var q = from nm in dt.AsEnumerable()
                    group nm by
                        new { key1 = nm.Field<decimal>("NSRDZDAH"), key2 = nm.Field<string>("NSRBM"), key3 = nm.Field<string>("NSRMC"), key4 = nm.Field<string>("NSRSBH") } into g
                    select new
                    {
                        NSRDZDAH = g.Key.key1,
                        NSRBM = g.Key.key2,
                        NSRMC = g.Key.key3,
                        NSRSBH = g.Key.key4
                    };

            BLL.TaxClassAllowance tBLL = new BLL.TaxClassAllowance();
            List<Model.TaxClassAllowance> tl = tBLL.GetModelList(" FYear=" + y);
            DataTable rDt = new DataTable();
            rDt.Columns.Add("NSRDZDAH");
            rDt.Columns.Add("NSRBM");
            rDt.Columns.Add("NSRMC");
            rDt.Columns.Add("NSRSBH");
            rDt.Columns.Add("FEnterprisePropNo");
            rDt.Columns.Add("FFinancePropName");
            
            foreach (Model.TaxClassAllowance t in tl)
            {
                rDt.Columns.Add(t.FTaxClassAllowanceNo, typeof(decimal));
            }

            foreach (var n in q)
            {
                DataRow rDr = rDt.NewRow();
                DataRow[] nDr = dt.Select(" NSRDZDAH =" + n.NSRDZDAH.ToString());
                DataTable nDt = nDr.CopyToDataTable();
                foreach (DataColumn dc in rDt.Columns)
                {
                    DataRow[] dtrl = nDt.Select(" FTaxClassAllowanceNo='" + dc.ColumnName + "'");
                    if (dtrl.Count() > 0)
                    {
                        DataTable table = dtrl.CopyToDataTable();
                        rDr[dc.ColumnName] = table.Compute("Sum(se)", "true");
                    }
                    else
                    {
                        rDr[dc.ColumnName] = 0;
                    }

                }
                rDr["NSRDZDAH"] = n.NSRDZDAH.ToString();
                rDr["NSRBM"] = n.NSRBM.ToString();
                rDr["NSRMC"] = n.NSRMC.ToString();
                rDr["NSRSBH"] = n.NSRSBH.ToString();
                rDr["FEnterprisePropNo"] = nDr[0]["FEnterprisePropNo"];
                rDr["FFinancePropName"] = nDr[0]["FFinancePropName"];
                rDt.Rows.Add(rDr);
            }

            return rDt;
        }

        /// <summary>
        /// 公式判断数据格式
        /// </summary>
        /// <returns></returns>
        public DataTable GetComputeExpressionDT()
        {
            DataTable cDt = new DataTable();
            cDt.Columns.Add("se", typeof(decimal));
            cDt.Columns.Add("base", typeof(decimal));
            cDt.Columns.Add("rateIn", typeof(decimal));
            cDt.Columns.Add("rateOut", typeof(decimal));
            cDt.Columns.Add("zcjd");
            return cDt;
        }

        public System.Web.Mvc.FileResult ToExecl()
        {
            string issNo = Fetch.Post("hidissueID");
            DataTable dt = new DataTable();
            string fyear = OperateSession.get(Common.GlobalConstant.PARAM_SYSYEAR);
            BLL.VSBZSXX VSBZSXXBLL = new BLL.VSBZSXX();
            DataSet ds = VSBZSXXBLL.GetDataforQueryAllowanceForecast((decimal.Parse(fyear)) + "-01-01", (decimal.Parse(fyear)) + "-12-31", (decimal.Parse(fyear)).ToString());
            BLL.TaxAuditAdjust TaxAuditAdjustBLL = new BLL.TaxAuditAdjust();
            DataSet yds = TaxAuditAdjustBLL.GetAllDateListByYear((decimal.Parse(fyear) - 1).ToString());
            DataTable dts = GetPolicyData(ds.Tables[0], yds.Tables[0], (decimal.Parse(fyear)).ToString());


            dt = GetShowData(fyear, dts);


            BLL.TaxClassAllowance taxClassAllowanceBLL = new BLL.TaxClassAllowance();
            List<Model.TaxClassAllowance> taxClassAllowanceList = taxClassAllowanceBLL.GetModelList("FYear=" + fyear);

            int totalRowCount = 2;
            List<Model.XlsModel> xlsModelList = new List<Model.XlsModel>();

            int colCount = 4;

            Model.XlsModel xlsModel = new Model.XlsModel();
            xlsModel.RowSpan = 2;
            xlsModel.RowIndex = totalRowCount;
            xlsModel.Title = "序号";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;

            xlsModelList.Add(xlsModel);

            xlsModel = new Model.XlsModel();
            xlsModel.RowSpan = 2;
            xlsModel.RowIndex = totalRowCount;
            xlsModel.Title = "纳税编码";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModelList.Add(xlsModel);

            xlsModel = new Model.XlsModel();
            xlsModel.RowSpan = 2;
            xlsModel.RowIndex = totalRowCount;
            xlsModel.Title = "企业名称";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModelList.Add(xlsModel);

            xlsModel = new Model.XlsModel();
            xlsModel.RowSpan = 2;
            xlsModel.RowIndex = totalRowCount;
            xlsModel.Title = "资金项目";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModelList.Add(xlsModel);

            xlsModel = new Model.XlsModel();
            xlsModel.ColSpan = taxClassAllowanceList.Count + 2;
            xlsModel.RowIndex = totalRowCount;
            xlsModel.Title = "本年实绩";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModelList.Add(xlsModel);
            colCount += taxClassAllowanceList.Count;

                foreach (Model.TaxClassAllowance t in taxClassAllowanceList)
                {
                    if (t.FTaxClassAllowanceName.Contains("增值税"))
                    {
                        if (t.FTaxClassAllowanceName.Contains("营改"))
                        {
                            xlsModel = new Model.XlsModel();
                            xlsModel.RowSpan = 2;
                            xlsModel.RowIndex = totalRowCount;
                            xlsModel.Title = t.FTaxClassAllowanceName + "补贴率";
                            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                            xlsModelList.Add(xlsModel);
                            colCount += 1;
                        }
                        else
                        {
                            xlsModel = new Model.XlsModel();
                            xlsModel.ColSpan = 3;
                            xlsModel.RowIndex = totalRowCount;
                            xlsModel.Title = t.FTaxClassAllowanceName;
                            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                            xlsModelList.Add(xlsModel);
                            colCount += 3;
                        }

                    }
                    else
                    {
                        xlsModel = new Model.XlsModel();
                        xlsModel.RowSpan = 2;
                        xlsModel.RowIndex = totalRowCount;
                        xlsModel.Title = t.FTaxClassAllowanceName + "补贴率";
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModelList.Add(xlsModel);
                        colCount += 1;
                    }
                }
          
            xlsModel = new Model.XlsModel();
            xlsModel.ColSpan = taxClassAllowanceList.Count + 1;
            xlsModel.RowIndex = totalRowCount;
            xlsModel.Title = "可补贴金额";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModelList.Add(xlsModel);
            colCount += taxClassAllowanceList.Count;



            xlsModel = new Model.XlsModel();
            xlsModel.ColSpan = colCount;
            xlsModel.RowIndex = 1;
            xlsModel.BottomLineStyle = 0;
            xlsModel.RightLineStyle = 0;
            xlsModel.LeftLineStyle = 0;
            xlsModel.TopLineStyle = 0;
            xlsModel.Title = "制表日期：" + DateTime.Now.ToString("yyyy年MM月dd日");
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModelList.Add(xlsModel);

            xlsModel = new Model.XlsModel();
            xlsModel.ColSpan = 3;
            xlsModel.RowIndex = 1;
            xlsModel.BottomLineStyle = 0;
            xlsModel.RightLineStyle = 0;
            xlsModel.LeftLineStyle = 0;
            xlsModel.TopLineStyle = 0;
            xlsModel.Title = "（税收金额单位为元  补贴金额单位为万元）";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModelList.Add(xlsModel);

            totalRowCount++;

            foreach (Model.TaxClassAllowance t in taxClassAllowanceList)
            {
                xlsModel = new Model.XlsModel();
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = t.FTaxClassAllowanceName;
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
            }

            xlsModel = new Model.XlsModel();
            xlsModel.RowIndex = totalRowCount;
            xlsModel.Title = "营改增补贴";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModelList.Add(xlsModel);

            xlsModel = new Model.XlsModel();
            xlsModel.RowIndex = totalRowCount;
            xlsModel.Title = "合计";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModelList.Add(xlsModel);


            xlsModel = new Model.XlsModel();
            xlsModel.RowIndex = totalRowCount;
            xlsModel.Title = "基数";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModelList.Add(xlsModel);
            xlsModel = new Model.XlsModel();
            xlsModel.RowIndex = totalRowCount;
            xlsModel.Title = "基数内补贴率";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModelList.Add(xlsModel);
            xlsModel = new Model.XlsModel();
            xlsModel.RowIndex = totalRowCount;
            xlsModel.Title = "基数外补贴率(补贴率)";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModelList.Add(xlsModel);


            foreach (EnterpriseFund.Model.TaxClassAllowance t in taxClassAllowanceList)
            {
                xlsModel = new Model.XlsModel();
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = t.FTaxClassAllowanceName;
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
            }

            xlsModel = new Model.XlsModel();
            xlsModel.RowIndex = totalRowCount;
            xlsModel.Title = "小计";
            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
            xlsModelList.Add(xlsModel);


            for (int i = 0; i < dt.Rows.Count; i++)
            {
                totalRowCount++;
                foreach (System.Data.DataColumn dc in dt.Columns)
                {
                    decimal d = 0;
                    if (decimal.TryParse(dt.Rows[i][dc.ColumnName].ToString(), out d))
                    {
                        if (d != 0)
                        {
                            xlsModel = new Model.XlsModel();
                            xlsModel.RowIndex = totalRowCount;
                            if (dc.ColumnName == "xh")
                            {
                                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                            }
                            else
                            {
                                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Right;
                            }
                            xlsModel.Title = dt.Rows[i][dc.ColumnName].ToString();
                            xlsModelList.Add(xlsModel);
                        }
                        else
                        {
                            xlsModel = new Model.XlsModel();
                            xlsModel.RowIndex = totalRowCount;
                            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Right;
                            xlsModel.Title = "";
                            xlsModelList.Add(xlsModel);
                        }
                    }
                    else
                    {
                        xlsModel = new Model.XlsModel();
                        xlsModel.RowIndex = totalRowCount;
                        xlsModel.Title = dt.Rows[i][dc.ColumnName].ToString();
                        if (dc.ColumnName == "NSRMC")
                        {
                            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Left;
                        }
                        else
                        {
                            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        }
                        xlsModelList.Add(xlsModel);
                    }
                }
            }

            BLL.WorkFlowProcess workFlowProcessBLL = new BLL.WorkFlowProcess();


            totalRowCount++;
         

            List<Model.XlsModel_Column> xlsModelColumns = new List<Model.XlsModel_Column>();
            xlsModelColumns.Add(new Model.XlsModel_Column()
            {
                ColumnIndexStart = 0,
                ColumnIndexEnd = 30,
                Width = 10
            });

            List<Model.XlsModel_Row> xlsModelRows = new List<Model.XlsModel_Row>();
            ushort rowCount = (ushort)(totalRowCount);
            xlsModelRows.Add(new Model.XlsModel_Row()
            {
                RowIndexStart = 1,
                RowIndexEnd = 1,
                RowHeight = 23
            });

            xlsModelRows.Add(new Model.XlsModel_Row()
            {
                RowIndexStart = 2,
                RowIndexEnd = 7,
                RowHeight = 12

            });
            xlsModelRows.Add(new Model.XlsModel_Row()
            {
                RowIndexStart = 8,
                RowIndexEnd = rowCount,
                RowHeight = 15

            });

            Model.ExcelTitleFont excelTitleFont = new Model.ExcelTitleFont()
            {
                FontName = "宋体",
                FontSize = 18,
                Bold = true
            };

            byte[] b = Common.ExcelClass.ExportToExcelReturnByte("", "企业资金扶持年度预测汇总表 ", "企业资金扶持年度预测汇总表", excelTitleFont, totalRowCount, xlsModelList, xlsModelColumns, xlsModelRows);

            string name = "企业扶持资金审核汇总表.xls";
            return File(b, "application/vnd.ms-excel", HttpUtility.UrlEncode(name));

        }
    }
}
